package gew.beans;

import gew.data.DimensionValue;
import gew.data.IndicatorGraphItem;
import gew.data.IndicatorSummaryIndicator;
import gew.data.IndicatorSummaryItem;
import gew.data.IndicatorSummaryItemDetail;
import gew.data.IndicatorTableItem;
import gew.data.LoadRequest;
import gew.data.TradeSummaryItem;
import gew.utils.SqlUtils;
import gew.data.MenuItem;
import gew.data.TradeTopNPartnersData;
import gew.data.TradeTopNPartnersDataItem;
import gew.table.TableDimensionValue;
import gew.table.TableRow;
import gew.data.TreeItem;
import gew.table.Table;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.ResourceBundle;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.ejb.Asynchronous;
import javax.ejb.Stateless;

@Stateless
public class DataFacade2 {

    private static final String BUNDLE_NAME = "sql";
    private static final String CONNECTION_URL = "CONNECTION_URL";
    private static final String CONNECTION_DRIVERCLASS = "CONNECTION_DRIVERCLASS";
    private static final String CONNECTION_PORTNUMBER = "CONNECTION_PORTNUMBER";
    private static final String CONNECTION_DATABASENAME = "CONNECTION_DATABASENAME";
    private static final String CONNECTION_USER = "CONNECTION_USER";
    private static final String CONNECTION_SERVERNAME = "CONNECTION_SERVERNAME";
    private static final String CONNECTION_CHARACTERSETRESULTS = "CONNECTION_CHARACTERSETRESULTS";
    private static final String CONNECTION_CHARACTERENCODING = "CONNECTION_CHARACTERENCODING";
    private static final String CONNECTION_PASSWORD = "CONNECTION_PASSWORD";

    private static final String DB_URL = ResourceBundle.getBundle(BUNDLE_NAME).getString(CONNECTION_URL);
    private static final String DB_DRIVER_CLASS = ResourceBundle.getBundle(BUNDLE_NAME).getString(CONNECTION_DRIVERCLASS);

    private static final Properties DB_INFO = new Properties(getDefaultProperties());

    private static Properties getDefaultProperties() {
        Properties result = new Properties();
        result.put("url", DB_URL);
        result.put("driverClass", DB_DRIVER_CLASS);
        result.put("portNumber", ResourceBundle.getBundle(BUNDLE_NAME).getString(CONNECTION_PORTNUMBER));
        result.put("databaseName", ResourceBundle.getBundle(BUNDLE_NAME).getString(CONNECTION_DATABASENAME));
        result.put("user", ResourceBundle.getBundle(BUNDLE_NAME).getString(CONNECTION_USER));
        result.put("serverName", ResourceBundle.getBundle(BUNDLE_NAME).getString(CONNECTION_SERVERNAME));
        result.put("characterSetResults", ResourceBundle.getBundle(BUNDLE_NAME).getString(CONNECTION_CHARACTERSETRESULTS));
        result.put("characterEncoding", ResourceBundle.getBundle(BUNDLE_NAME).getString(CONNECTION_CHARACTERENCODING));
        result.put("password", ResourceBundle.getBundle(BUNDLE_NAME).getString(CONNECTION_PASSWORD));
        return result;
    }

    public static Connection getDBConnection() {
        Connection dbConnection = null;
        try {
            Class.forName(DB_DRIVER_CLASS);
        } catch (ClassNotFoundException e) {
            System.out.println(e.getMessage());
        }

        try {
            dbConnection = DriverManager.getConnection(DB_URL, DB_INFO);
            return dbConnection;

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return dbConnection;
    }

    public List<Object[]> getSqlQueryResults(String query) {
        List<Object[]> result = new ArrayList<>();
        Connection conn = getDBConnection();
        try {
            Statement st = conn.createStatement();
            String[] stmts = query.split(SqlUtils.SEMICOLON);
            for (int i = 0; i < stmts.length - 1; i++) {
                if (!stmts[i].isEmpty() && i != stmts.length - 1) {
                    st.executeQuery(stmts[i]);
                }
            }
            if (stmts.length > 0) {
                ResultSet rs = st.executeQuery(stmts[stmts.length - 1]);
                while (rs.next()) {
                    Object[] row;
                    row = new Object[rs.getMetaData().getColumnCount()];
                    for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                        row[i - 1] = rs.getObject(i);
                    }
                    result.add(row);
                }
            }
        } catch (Exception e) {
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, e.getMessage());
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, query);
            System.out.println(e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return result;
    }

    public boolean executeStatements(String query) {
        boolean result = true;
        Connection conn = getDBConnection();
        try {
            Statement st = conn.createStatement();
            String[] stmts = query.split(SqlUtils.SEMICOLON);
            for (int i = 0; i < stmts.length; i++) {
                if (!stmts[i].replace(SqlUtils.SPACE, "").isEmpty()) {
                    st.execute(stmts[i]);
                }
            }
        } catch (Exception e) {
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, e.getMessage());
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, query);
            System.out.println(e.getMessage());
            result = false;
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, null, ex);
                    result = false;
                }
            }
        }
        return result;
    }

    public List<String> executeQueryToListOfString(String query) {
        List<String> result = new ArrayList<>();
        Connection conn = getDBConnection();
        try {
            Statement st = conn.createStatement();
            String[] stmts = query.split(SqlUtils.SEMICOLON);
            for (int i = 0; i < stmts.length - 1; i++) {
                if (!stmts[i].isEmpty() && i != stmts.length - 1) {
                    st.executeQuery(stmts[i]);
                }
            }
            if (stmts.length > 0) {
                ResultSet rs = st.executeQuery(stmts[stmts.length - 1]);
                while (rs.next()) {
                    result.add(rs.getString(1));
                }
            }
        } catch (Exception e) {
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, e.getMessage());
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, query);
            System.out.println(e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return result;
    }

    public Integer getSingleResult(String query) {
        Integer result = null;
        Connection conn = getDBConnection();
        try {
            Statement st = conn.createStatement();
            String[] stmts = query.split(SqlUtils.SEMICOLON);
            for (int i = 0; i < stmts.length - 1; i++) {
                if (!stmts[i].isEmpty() && i != stmts.length - 1) {
                    st.executeQuery(stmts[i]);
                }
            }
            if (stmts.length > 0) {
                ResultSet rs = st.executeQuery(stmts[stmts.length - 1]);
                if (rs.next()) {
                    result = rs.getInt(1);
                }
            }
        } catch (Exception e) {
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, e.getMessage());
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, query);
            System.out.println(e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return result;
    }

    public List<TradeSummaryItem> getTradeSummaryItems(String query) {
        List<TradeSummaryItem> result = new ArrayList<>();
        Connection conn = getDBConnection();
        try {
            Statement st = conn.createStatement();
            String[] stmts = query.split(SqlUtils.SEMICOLON);
            for (int i = 0; i < stmts.length - 1; i++) {
                if (!stmts[i].isEmpty() && i != stmts.length - 1) {
                    st.executeQuery(stmts[i]);
                }
            }
            if (stmts.length > 0) {
                ResultSet rs = st.executeQuery(stmts[stmts.length - 1]);
                while (rs.next()) {
                    result.add(new TradeSummaryItem(rs));
                }
            }
        } catch (Exception e) {
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, e.getMessage());
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, query);
            System.out.println(e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return result;
    }

    public List<MenuItem> getMenuItems(String query) {
        List<MenuItem> result = new ArrayList<>();
        Connection conn = getDBConnection();
        try {
            Statement st = conn.createStatement();
            String[] stmts = query.split(SqlUtils.SEMICOLON);
            for (int i = 0; i < stmts.length - 1; i++) {
                if (!stmts[i].isEmpty() && i != stmts.length - 1) {
                    st.executeQuery(stmts[i]);
                }
            }
            if (stmts.length > 0) {
                ResultSet rs = st.executeQuery(stmts[stmts.length - 1]);
                while (rs.next()) {
                    result.add(new MenuItem(rs));
                }
            }
        } catch (Exception e) {
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, e.getMessage());
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, query);
            System.out.println(e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return result;
    }

    public LoadRequest getLoadRequest(String query) {
        LoadRequest result = null;
        Connection conn = getDBConnection();
        try {
            Statement st = conn.createStatement();
            String[] stmts = query.split(SqlUtils.SEMICOLON);
            for (int i = 0; i < stmts.length - 1; i++) {
                if (!stmts[i].isEmpty() && i != stmts.length - 1) {
                    st.executeQuery(stmts[i]);
                }
            }
            if (stmts.length > 0) {
                ResultSet rs = st.executeQuery(stmts[stmts.length - 1]);
                if (rs.next()) {
                    result = new LoadRequest(
                            rs.getInt(LoadRequest.SQL_ID + 1), rs.getString(LoadRequest.SQL_URL + 1), rs.getString(LoadRequest.SQL_FILENAME + 1), rs.getDate(LoadRequest.SQL_START + 1), rs.getDate(LoadRequest.SQL_END + 1), rs.getInt(LoadRequest.SQL_STATUS + 1), rs.getInt(LoadRequest.SQL_COUNT + 1)
                    );
                }
            }
        } catch (Exception e) {
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, e.getMessage());
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, query);
            System.out.println(e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return result;
    }

    public int executeUpdate(String query) {
        int result = 0;
        Connection conn = getDBConnection();
        try {
            Statement st = conn.createStatement();
            String[] stmts = query.split(SqlUtils.SEMICOLON);
            for (int i = 0; i < stmts.length; i++) {
                if (!stmts[i].isEmpty()) {
                    result = result + st.executeUpdate(stmts[i]);
                }
            }
        } catch (Exception e) {
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, e.getMessage());
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, query);
            System.out.println(e.getMessage());
            result = -1;
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, null, ex);
                    result = -1;
                }
            }
        }
        return result;
    }

    public Object[] getSqlQueryFirstResult(String query) {
        Object[] result = null;
        Connection conn = getDBConnection();
        try {
            Statement st = conn.createStatement();
            String[] stmts = query.split(SqlUtils.SEMICOLON);
            for (int i = 0; i < stmts.length - 1; i++) {
                if (!stmts[i].isEmpty() && i != stmts.length - 1) {
                    st.executeQuery(stmts[i]);
                }
            }
            if (stmts.length > 0) {
                st.setFetchSize(1);
                ResultSet rs = st.executeQuery(stmts[stmts.length - 1]);
                if (rs.next()) {
                    result = new Object[rs.getMetaData().getColumnCount()];
                    for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                        result[i - 1] = rs.getObject(i);
                    }
                }
            }
        } catch (Exception e) {
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, e.getMessage());
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, query);
            System.out.println(e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return result;
    }

    public Object getSqlQuerySingleResult(String query) {
        Object result = null;
        Connection conn = getDBConnection();
        try {
            Statement st = conn.createStatement();
            String[] stmts = query.split(SqlUtils.SEMICOLON);
            for (int i = 0; i < stmts.length - 1; i++) {
                if (!stmts[i].isEmpty() && i != stmts.length - 1) {
                    st.executeQuery(stmts[i]);
                }
            }
            if (stmts.length > 0) {
                st.setFetchSize(1);
                ResultSet rs = st.executeQuery(stmts[stmts.length - 1]);
                if (rs.next()) {
                    if (rs.getMetaData().getColumnCount() > 0) {
                        result = rs.getObject(1);
                    }
                }
            }
        } catch (Exception e) {
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, e.getMessage());
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, query);
            System.out.println(e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return result;
    }

    public ArrayList<IndicatorSummaryItem> getIndicatorSummaryItems(String query) {
        ArrayList<IndicatorSummaryItem> result = new ArrayList<>();
        int prevCountryId = -1;
        Connection conn = getDBConnection();
        try {
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            int index = 0;
            while (rs.next()) {
                IndicatorSummaryItemDetail item = new IndicatorSummaryItemDetail(rs);
                if (item.getCountryId() != prevCountryId) {
                    index++;
                    prevCountryId = item.getCountryId();
                    result.add(new IndicatorSummaryItem(index, item.getCountryId(), item.getCountryName(), item.getCountryUrl()));
                }
                result.get(result.size() - 1).addDetail(item);
            }
        } catch (Exception e) {
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, e.getMessage());
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, query);
            System.out.println(e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }

        return result;
    }

    public ArrayList<IndicatorSummaryIndicator> getIndicatorSummaryIndicators(String query) {
        ArrayList<IndicatorSummaryIndicator> result = new ArrayList<>();
        Connection conn = getDBConnection();
        try {
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                IndicatorSummaryIndicator item = new IndicatorSummaryIndicator(rs);
                result.add(item);
            }
        } catch (Exception e) {
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, e.getMessage());
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, query);
            System.out.println(e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }

        return result;
    }

    public ArrayList<TreeItem> getTreeItems(String query) {
        ArrayList<TreeItem> result = new ArrayList<>();
        Connection conn = getDBConnection();
        try {
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                TreeItem item = new TreeItem(rs);
                result.add(item);
            }
        } catch (Exception e) {
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, e.getMessage());
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, query);
            System.out.println(e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }

        return result;
    }

    public ArrayList<IndicatorGraphItem> getIndicatorGraphItems(String query) {
        ArrayList<IndicatorGraphItem> result = new ArrayList<>();
        Connection conn = getDBConnection();
        try {
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            int colCount = rs.getMetaData().getColumnCount();
            while (rs.next()) {
                IndicatorGraphItem item = new IndicatorGraphItem(rs, colCount);
                result.add(item);
            }
        } catch (Exception e) {
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, e.getMessage());
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, query);
            System.out.println(e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }

        return result;
    }

    public ArrayList<IndicatorTableItem> getIndicatorTableItems(String query) {
        ArrayList<IndicatorTableItem> result = new ArrayList<>();
        Connection conn = getDBConnection();
        try {
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            int colCount = rs.getMetaData().getColumnCount();
            while (rs.next()) {
                result.add(new IndicatorTableItem(rs, colCount));
            }
        } catch (Exception e) {
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, e.getMessage());
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, query);
            System.out.println(e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }

        return result;
    }

    public ArrayList<ArrayList<TableDimensionValue>> getTableDimensionValues(List<String> queries) {
        ArrayList<ArrayList<TableDimensionValue>> result = new ArrayList<>();
        Connection conn = getDBConnection();
        try {
            Statement st = conn.createStatement();

            for (int i = 0; i < queries.size(); i++) {
                ResultSet rs = st.executeQuery(queries.get(i));
                ArrayList<TableDimensionValue> r = new ArrayList<>();
                while (rs.next()) {
                    r.add(new TableDimensionValue(rs));
                }
                result.add(r);
            }
        } catch (Exception e) {
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, e.getMessage());
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, String.join(SqlUtils.NEW_LINE, queries));
            System.out.println(e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }

        return result;
    }

    public ArrayList<TableRow> getTableMeasureValues(String query, Table table) {
        ArrayList<TableRow> result = new ArrayList<>();
        Connection conn = getDBConnection();
        int fRowId = 1;
        try {
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            int colCount = rs.getMetaData().getColumnCount();
            int rowIndex = 0;
            while (rs.next()) {
                String rowId = rs.getString(fRowId);
                TableRow oldRow = table.getRowById(rowId);
                TableRow newRow = new TableRow(oldRow, rowIndex);
                for (int i = fRowId + 1; i <= colCount; i++) {
                    newRow.addVDimCell(rs.getString(i), table.getColumns().get(i - fRowId - 1));
                }
                result.add(newRow);
                rowIndex++;
            }
        } catch (Exception e) {
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, e.getMessage());
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, String.join(SqlUtils.NEW_LINE, query));
            System.out.println(e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return result;
    }

    public TradeTopNPartnersData getTradeTopNPartnersData(String query) {
        TradeTopNPartnersData result = new TradeTopNPartnersData();
        Connection conn = getDBConnection();
        try {
            Statement st = conn.createStatement();
            String[] stmts = query.split(SqlUtils.SEMICOLON);
            for (int i = 0; i < stmts.length - 1; i++) {
                if (!stmts[i].isEmpty() && i != stmts.length - 1) {
                    st.executeQuery(stmts[i]);
                }
            }
            if (stmts.length > 0) {
                ResultSet rs = st.executeQuery(stmts[stmts.length - 1]);
                while (rs.next()) {
                    if (rs.getMetaData().getColumnCount() > 0) {
                        TradeTopNPartnersDataItem item = new TradeTopNPartnersDataItem(rs);
                        result.addCol(item.getPartnerId());
                        result.addDataItem(item);
                    }
                }
            }
        } catch (Exception e) {
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, e.getMessage());
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, query);
            System.out.println(e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return result;
    }

    public ArrayList<DimensionValue> getDimensionValues(String query) {
        ArrayList<DimensionValue> result = new ArrayList<>();
        Connection conn = getDBConnection();
        try {
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                result.add(new DimensionValue(rs));
            }
        } catch (Exception e) {
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, e.getMessage());
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, query);
            System.out.println(e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }

        return result;
    }

    @Asynchronous
    public void logRequest(final ArrayList<String> stmts) {
        Connection conn = getDBConnection();
        try {
            Statement st = conn.createStatement();
            for (String stmt: stmts) {
                if (!stmt.isEmpty()) {
                    st.executeUpdate(stmt);
                }
            }
        } catch (Exception e) {
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, e.getMessage());
            Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, String.join(SqlUtils.NEW_LINE, stmts));
            System.out.println(e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataFacade2.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }
}
